﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
namespace LdExpress
{
    public class CustomerActive
    {
        public long ID = -1;
        public string Goods = "";
        public string GoodsEx = "";
        public string HsGoods = "";
        public string HsGoodsEx = "";
        public string Name = "";
        public string Code = ""; // Lay MST lam code
        public string Address = "";
        public string Tel = "";
        public string Mobile = "";
        public string Email = "";
        public string Social = "";
        public string Pic = "";
        public long PicID = 0;
        public string Position = "";
        public string Routers = "";
        public string Vols = "";
        public long ListType = -1; //1: Khach hang dang di,2:Khach hang deo duoi,3:Khach hang suu tam ,4:khach hang bo qua       
        public long UserID = -1; //-1:customer store
        public string Description = "";
        public int Sort = 0;
        public long Owner = 0;
        public long Duplicate = 0;
        public long Level = 0;   // Customer Level 1: Vip, 2:regular, 3: General     
        public long AutoLevel = 0;   // Customer Level by System 1: Vip, 2:regular, 3: General     
        public DateTime LastUpdate = DateTime.Now;
        public string Pol = "";
        public string Report = "";
        public string CusLevel_Limit = "";
        public string CusLevel_Liabilities = "";
        public string CusLevel_Note = "";
        public string CusLevel_TB = "";
        public string CusLevel_Debts_Incurred = "";
        public string CusLevel_Contract_StartDate = "";
        public string CusLevel_Contract_EndDate = "";
        public int Archive = 0; //Ative Cust:2, Potential: 0, Black List: 1; 
        public int FollowUpLevel = 0;
        public int Vols_Ext = 0;
        public string Vols_Type = "";
        public string Routers_Ext = "";
        public float Active_Value = 0;
        public string Active_Type = "";
        public string Active_Dlvry = "";
        public string Active_Cnee = "";
        public string Country = "";
        public int ProvinceID = 0;
        public CustomerActive()
        {
        }

        public CustomerActive(long lID)
        {
            this.ID = lID;
        }

        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_CustomerActive";
            bool bUpdateDup = false;
            GV.ErrorMessage = "";
            if (ID <= 0) // new object, we insert new record to database
            {

                strSQL = "Insert into " + strTableName + " (" +
                            "`Goods`" +
                            ",`GoodsEx`" +
                            ",`HsGoods`" +
                            ",`HsGoodsEx`" +
                            ",`Name`" +
                            ",`Code`" +
                            ",`Address`" +
                            ",`Tel`" +
                            ",`Mobile`" +
                            ",`Email`" +
                            ",`Social`" +
                            ",`Pic`" +
                            ",`Position`" +
                            ",`ListType`" +
                            ",`UserID`" +
                            ",`Description`" +
                            ",`Routers`" +
                            ",`Vols`" +
                            ",`Sort`" +
                            ",`LastUpdate`" +
                            ",`Owner`" +
                            ",`Duplicate`" +
                            ",`Level`" +
                            ",`AutoLevel`" +
                            ",`Pol`" +
                            ",`Report`" +
                            ",`CusLevel_Limit`" +
                            ",`CusLevel_Liabilities`" +
                            ",`CusLevel_Note`" +
                            ",`CusLevel_Debts_Incurred`" +
                            ",`CusLevel_TB`" +
                            ",`CusLevel_Contract_StartDate`" +
                            ",`CusLevel_Contract_EndDate`" +
                            ",`Archive`" +
                            ",`PicID`" +
                            ",`Vols_Ext`" +
                            ",`Vols_Type`" +
                            ",`Routers_Ext`" +
                            ",`Active_Value`" +
                            ",`Active_Type`" +
                            ",`Active_Dlvry`" +
                            ",`Active_Cnee`" +
                            ",`Country`" +
                            ",`ProvinceID`" +
                            ") " +
                         " values(" +
                         "'" + DBModule.RefineString(Goods) + "'" +
                         ",'" + DBModule.RefineString(GoodsEx) + "'" +
                         ",'" + DBModule.RefineString(HsGoods) + "'" +
                         ",'" + DBModule.RefineString(HsGoodsEx) + "'" +
                             ",'" + DBModule.RefineString(Name) + "'" +
                             ",'" + DBModule.RefineString(Code) + "'" +
                             "," + "'" + DBModule.RefineString(Address) + "'" +
                             "," + "'" + DBModule.RefineString(Tel) + "'" +
                             "," + "'" + DBModule.RefineString(Mobile) + "'" +
                             "," + "'" + DBModule.RefineString(Email) + "'" +
                             "," + "'" + DBModule.RefineString(Social) + "'" +
                             "," + "'" + DBModule.RefineString(Pic) + "'" +
                             "," + "'" + DBModule.RefineString(Position) + "'" +
                             "," + ListType.ToString() +
                             "," + UserID.ToString() +
                             "," + "'" + DBModule.RefineString(Description) + "'" +
                             "," + "'" + DBModule.RefineString(Routers) + "'" +
                             "," + "'" + DBModule.RefineString(Vols) + "'" +
                             "," + Sort.ToString() +
                             "," + DBModule.RefineDatetime(LastUpdate, true) +
                             "," + Owner.ToString() +
                             "," + Duplicate.ToString() +
                             "," + Level.ToString() +
                             "," + AutoLevel.ToString() +
                             ",'" + DBModule.RefineString(Pol.ToString()) + "'" +
                             ",'" + DBModule.RefineString(Report.ToString()) + "'" +
                             ",'" + DBModule.RefineString(CusLevel_Limit.ToString()) + "'" +
                             ",'" + DBModule.RefineString(CusLevel_Liabilities.ToString()) + "'" +
                             ",'" + DBModule.RefineString(CusLevel_Note.ToString()) + "'" +
                             ",'" + DBModule.RefineString(CusLevel_Debts_Incurred.ToString()) + "'" +
                             ",'" + DBModule.RefineString(CusLevel_TB.ToString()) + "'" +
                             ",'" + DBModule.RefineString(CusLevel_Contract_StartDate.ToString()) + "'" +
                             ",'" + DBModule.RefineString(CusLevel_Contract_EndDate.ToString()) + "'" +
                             ",'" + Archive.ToString() + "'" +
                             ",'" + PicID.ToString() + "'" +
                             ",'" + Vols_Ext.ToString() + "'" +
                             ",'" + DBModule.RefineString(Vols_Type.ToString()) + "'" +
                             ",'" + DBModule.RefineString(Routers_Ext.ToString()) + "'" +
                             ",'" + Active_Value.ToString() + "'" +
                             ",'" + DBModule.RefineString(Active_Type.ToString()) + "'" +
                             ",'" + DBModule.RefineString(Active_Dlvry.ToString()) + "'" +
                             ",'" + DBModule.RefineString(Active_Cnee.ToString()) + "'" +
                             ",'" + DBModule.RefineString(Country) + "'" +
                             "," + ProvinceID.ToString() +
                             ")";

                // run SQL statement
                //throw new Exception(strSQL);                    
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("ActiveCust", "add", cn, trans);
            }
            else // edit object
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                            "Goods='" + DBModule.RefineString(Goods) + "'" +
                            ",GoodsEx='" + DBModule.RefineString(GoodsEx) + "'" +
                            ",HsGoods='" + DBModule.RefineString(HsGoods) + "'" +
                            ",HsGoodsEx='" + DBModule.RefineString(HsGoodsEx) + "'" +
                            ",Name='" + DBModule.RefineString(Name) + "'" +
                            ",Code='" + DBModule.RefineString(Code) + "'" +
                            ",Address='" + DBModule.RefineString(Address) + "'" +
                            ",Tel='" + DBModule.RefineString(Tel) + "'" +
                            ",Mobile='" + DBModule.RefineString(Mobile) + "'" +
                            ",Email='" + DBModule.RefineString(Email) + "'" +
                            ",Social='" + DBModule.RefineString(Social) + "'" +
                            ",Pic='" + DBModule.RefineString(Pic) + "'" +
                            ",Position='" + DBModule.RefineString(Position) + "'" +
                            ",ListType = " + ListType.ToString() +
                            ",UserID = " + UserID.ToString() +
                            ",Description='" + DBModule.RefineString(Description) + "'" +
                            ",Routers='" + DBModule.RefineString(Routers) + "'" +
                            ",Vols='" + DBModule.RefineString(Vols) + "'" +
                            ",Sort = " + Sort.ToString() +
                            ",LastUpdate=" + DBModule.RefineDatetime(LastUpdate, true) + "" +
                            ",Owner = " + Owner.ToString() +
                            ",Duplicate = " + Duplicate.ToString() +
                            ",Level = " + Level.ToString() +
                            ",AutoLevel = " + AutoLevel.ToString() +
                            ",Pol = '" + DBModule.RefineString(Pol.ToString()) + "'" +
                            ",Report = '" + DBModule.RefineString(Report.ToString()) + "'" +
                            ",CusLevel_Limit = '" + DBModule.RefineString(CusLevel_Limit.ToString()) + "'" +
                            ",CusLevel_Liabilities = '" + DBModule.RefineString(CusLevel_Liabilities.ToString()) + "'" +
                            ",CusLevel_Note = '" + DBModule.RefineString(CusLevel_Note.ToString()) + "'" +
                            ",CusLevel_Debts_Incurred = '" + DBModule.RefineString(CusLevel_Debts_Incurred.ToString()) + "'" +
                            ",CusLevel_TB = '" + DBModule.RefineString(CusLevel_TB.ToString()) + "'" +
                            ",CusLevel_Contract_StartDate = '" + DBModule.RefineString(CusLevel_Contract_StartDate.ToString()) + "'" +
                            ",CusLevel_Contract_EndDate = '" + DBModule.RefineString(CusLevel_Contract_EndDate.ToString()) + "'" +
                            ",Archive = " + Archive +
                            ",PicID = " + PicID +
                            ",Vols_Ext = " + Vols_Ext +
                            ",Vols_Type = '" + DBModule.RefineString(Vols_Type.ToString()) + "'" +
                            ",Routers_Ext = '" + DBModule.RefineString(Routers_Ext.ToString()) + "'" +
                            ",Active_Value = " + Active_Value +
                            ",Active_Type = '" + DBModule.RefineString(Active_Type.ToString()) + "'" +
                            ",Active_Dlvry = '" + DBModule.RefineString(Active_Dlvry.ToString()) + "'" +
                            ",Active_Cnee = '" + DBModule.RefineString(Active_Cnee.ToString()) + "'" +
                            ",Country = '" + DBModule.RefineString(Country) + "'" +
                            ",ProvinceID = " + ProvinceID +
                    " where ID=" + ID.ToString();

                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("ActiveCust", "edit", cn, trans);
            }
            //Update active if Code Existing in CRM or Black List
            bUpdateDup = IsActiveCodeExistingInCRM(Code, cn, trans);
            if (bUpdateDup)
            {
                strSQL = " Update " + strTableName + " set Duplicate=1 Where code='" + DBModule.RefineString(Code) + "'";
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
            }
            else if (Duplicate == 1)
            {
                strSQL = " Update " + strTableName + " set Duplicate=0 Where code='" + DBModule.RefineString(Code) + "'";
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
            }

        }
        public static void UpdateLastUpdate(long crmID, MySqlConnection cn, MySqlTransaction trans)
        {
            DBModule.ExecuteNonQuery("Update tbl_CustomerActive set LastUpdate=" + DBModule.RefineDatetime(DateTime.Now, true) + " Where id=" + crmID, cn, trans);
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "DELETE FROM tbl_CustomerActive WHERE ID = " + this.ID.ToString();
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("ActiveCust", "delete", cn, trans);
        }

        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "SELECT * FROM tbl_CustomerActive WHERE ID = " + ID.ToString() + " limit 0,1";
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("Goods")) Goods = dr["Goods"].ToString();
                if (!dr.IsNull("GoodsEx")) GoodsEx = dr["GoodsEx"].ToString();
                if (!dr.IsNull("HsGoods")) HsGoods = dr["HsGoods"].ToString();
                if (!dr.IsNull("HsGoodsEx")) HsGoodsEx = dr["HsGoodsEx"].ToString();
                if (!dr.IsNull("Name")) Name = dr["Name"].ToString();
                if (!dr.IsNull("Code")) Code = dr["Code"].ToString();
                if (!dr.IsNull("Address")) Address = dr["Address"].ToString();
                if (!dr.IsNull("Tel")) Tel = dr["Tel"].ToString();
                if (!dr.IsNull("Mobile")) Mobile = dr["Mobile"].ToString();
                if (!dr.IsNull("Email")) Email = dr["Email"].ToString();
                if (!dr.IsNull("Social")) Social = dr["Social"].ToString();
                if (!dr.IsNull("Pic")) Pic = dr["Pic"].ToString();
                if (!dr.IsNull("Position")) Position = dr["Position"].ToString();
                if (!dr.IsNull("ListType")) ListType = long.Parse(dr["ListType"].ToString());
                if (!dr.IsNull("UserID")) UserID = long.Parse(dr["UserID"].ToString());
                if (!dr.IsNull("Description")) Description = dr["Description"].ToString();
                if (!dr.IsNull("Routers")) Routers = dr["Routers"].ToString();
                if (!dr.IsNull("Vols")) Vols = dr["Vols"].ToString();
                if (!dr.IsNull("Sort")) Sort = int.Parse(dr["Sort"].ToString());
                if (!dr.IsNull("LastUpdate")) LastUpdate = DateTime.Parse(dr["LastUpdate"].ToString());
                if (!dr.IsNull("Owner")) Owner = long.Parse(dr["Owner"].ToString());
                if (!dr.IsNull("Duplicate")) Duplicate = long.Parse(dr["Duplicate"].ToString());
                if (!dr.IsNull("Level")) Level = long.Parse(dr["Level"].ToString());
                if (!dr.IsNull("AutoLevel")) AutoLevel = long.Parse(dr["AutoLevel"].ToString());
                if (!dr.IsNull("Pol")) Pol = dr["Pol"].ToString();
                if (!dr.IsNull("Report")) Report = dr["Report"].ToString();
                if (!dr.IsNull("CusLevel_Limit")) CusLevel_Limit = dr["CusLevel_Limit"].ToString();
                if (!dr.IsNull("CusLevel_Liabilities")) CusLevel_Liabilities = dr["CusLevel_Liabilities"].ToString();
                if (!dr.IsNull("CusLevel_Note")) CusLevel_Note = dr["CusLevel_Note"].ToString();
                if (!dr.IsNull("CusLevel_Debts_Incurred")) CusLevel_Debts_Incurred = dr["CusLevel_Debts_Incurred"].ToString();
                if (!dr.IsNull("CusLevel_TB")) CusLevel_TB = dr["CusLevel_TB"].ToString();
                if (!dr.IsNull("CusLevel_Contract_StartDate")) CusLevel_Contract_StartDate = dr["CusLevel_Contract_StartDate"].ToString();
                if (!dr.IsNull("CusLevel_Contract_EndDate")) CusLevel_Contract_EndDate = dr["CusLevel_Contract_EndDate"].ToString();
                if (!dr.IsNull("Archive")) Archive = int.Parse(dr["Archive"].ToString());
                if (!dr.IsNull("FollowUpLevel")) FollowUpLevel = int.Parse(dr["FollowUpLevel"].ToString());
                if (!dr.IsNull("PicID")) PicID = long.Parse(dr["PicID"].ToString());
                if (!dr.IsNull("Vols_Ext")) Vols_Ext = int.Parse(dr["Vols_Ext"].ToString());
                if (!dr.IsNull("Vols_Type")) Vols_Type = dr["Vols_Type"].ToString();
                if (!dr.IsNull("Routers_Ext")) Routers_Ext = dr["Routers_Ext"].ToString();
                if (!dr.IsNull("Active_Value")) Active_Value = float.Parse(dr["Active_Value"].ToString());
                if (!dr.IsNull("Active_Type")) Active_Type = dr["Active_Type"].ToString();
                if (!dr.IsNull("Active_Dlvry")) Active_Dlvry = dr["Active_Dlvry"].ToString();
                if (!dr.IsNull("Active_Cnee")) Active_Cnee = dr["Active_Cnee"].ToString();
                if (!dr.IsNull("Country")) Country = dr["Country"].ToString();
                if (!dr.IsNull("ProvinceID")) ProvinceID = int.Parse(dr["ProvinceID"].ToString());
            }
        }
        public static void GetList(string strCondition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out int nTotalItem, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            nTotalItem = 0;
            string strSQL = "";
            ds = null;

            // build SQL statement
            strSQL = "Select tbl_CustomerActive.*" +
                ",tbl_adminusers.Username as UserName,Goods.Name as GoodsName,GoodsEX.Name as GoodsExName" +
                " from tbl_CustomerActive left join tbl_adminusers on tbl_CustomerActive.UserID=tbl_adminusers.ID" +
                " LEFT JOIN tbl_goods Goods  ON tbl_CustomerActive.HsGoods=Goods.Code" +
                " LEFT JOIN tbl_goods GoodsEX  ON tbl_CustomerActive.HsGoodsEx=GoodsEX.Code" +
                " where 1=1 ";
            if ((strCondition != null) && (strCondition != ""))
            {
                strSQL += strCondition;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL += " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, out nTotalItem, cn, trans);

            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public static void GetListLevel(string strCondition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out int nTotalItem, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            nTotalItem = 0;
            string strSQL = "";
            ds = null;

            // build SQL statement
            strSQL = "Select *, " +
                " STR_TO_DATE(CusLevel_Contract_EndDate,'%d,%m,%Y') as dCusLevel_Contract_EndDate " +
                " ,IF(STR_TO_DATE(CusLevel_Contract_EndDate,'%d-%m-%Y') <= DATE_ADD(CURDATE(), INTERVAL 30 DAY) " +
                //" AND STR_TO_DATE(CusLevel_Contract_EndDate,'%d-%m-%Y') >= CURDATE()" +
                " AND CusLevel_Contract_EndDate <> ''" +
                ",1,0) as bContract_EndDate" +
                " from tbl_CustomerActive where 1=1 ";
            if ((strCondition != null) && (strCondition != ""))
            {
                strSQL += strCondition;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL += " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement

            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, out nTotalItem, cn, trans);

            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public static void GetListCount(string strCondition, out int nCount, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            // build SQL statement
            strSQL = "Select ID from tbl_CustomerActive where 1=1 ";
            if ((strCondition != null) && (strCondition != ""))
            {
                strSQL += strCondition;
            }
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count > 0)
                nCount = ds.Tables[0].Rows.Count;
            else
                nCount = 0;

        }
        public static void GetListCount(string strCondition, out int nCount, out long nID, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            // build SQL statement
            strSQL = "Select ID from tbl_CustomerActive where 1=1 ";
            if ((strCondition != null) && (strCondition != ""))
            {
                strSQL += strCondition;
            }
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count > 0)
            {
                nCount = ds.Tables[0].Rows.Count;
                nID = long.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
            }
            else
            {
                nCount = 0;
                nID = 0;
            }

        }

        public void DoSearch(string strKeywords, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select `ID` from tbl_CustomerActive where 1=1 ";

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }
        public static bool IsUniqueNameExisting(string uniqueName, long currentID, MySqlConnection con, MySqlTransaction trans)
        {
            bool retVal = false;
            string sqlCommand = "";
            // build SQL statement
            sqlCommand = "Select ID from tbl_CustomerActive " +
                "where ID <> " + currentID.ToString() + " AND Name = '" + DBModule.RefineString(uniqueName) + "' AND Archive<>2 limit 0,1";
            // run SQL statement
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
            if (ds != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    retVal = true;
                }
            }
            return retVal;
        }
        public static bool IsCodeExisting(string code, long currentID, MySqlConnection con, MySqlTransaction trans)
        {
            bool retVal = false;
            string sqlCommand = "";
            // build SQL statement
            sqlCommand = "Select ID from tbl_CustomerActive " +
                "where ID <> " + currentID.ToString() + " AND Code = '" + DBModule.RefineString(code) + "' AND code <> '' AND Archive<>2 limit 0,1";
            // run SQL statement
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
            if (ds != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    retVal = true;
                }
            }
            return retVal;
        }
        // Tinh trung khach voi BlackList va Dang di (listType=1 )
        public static bool IsUniqueNameActiveExisting(string uniqueName, long currentID, MySqlConnection con, MySqlTransaction trans)
        {
            bool retVal = false;
            string sqlCommand = "";
            // build SQL statement           
            sqlCommand = "Select ID from tbl_CustomerActive " +
                "where ID <> " + currentID.ToString() + " AND Name = '" + DBModule.RefineString(uniqueName) + "' AND (Archive=1 OR ListType=1)  limit 0,1";
            // run SQL statement
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
            if (ds != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    retVal = true;
                }
            }
            return retVal;
        }
        //Is existing by code
        public static bool IsCodeExistingExcludePO(string code, long currentID, MySqlConnection con, MySqlTransaction trans)
        {
            bool retVal = false;
            string sqlCommand = "";
            // build SQL statement
            sqlCommand = "Select ID from tbl_CustomerActive " +
                "where ID <> " + currentID.ToString() + " AND Code = '" + DBModule.RefineString(code) + "' AND (Archive <> 0 OR (Archive = 0 AND UserID > 0))  limit 0,1";
            // run SQL statement
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
            if (ds != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    retVal = true;
                }
            }
            return retVal;
        }
        //Is arive code is existing in CRM or Black List
        public static bool IsActiveCodeExistingInCRM(string code, MySqlConnection con, MySqlTransaction trans)
        {
            bool retVal = false;
            string sqlCommand = "";
            // build SQL statement
            sqlCommand = "Select ID from tbl_CustomerRelation " +
                "where Code = '" + DBModule.RefineString(code) + "' AND ((ARCHIVE=1 AND userid=-1) ||  userID >0)  limit 0,1";
            // run SQL statement
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
            if (ds != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    retVal = true;
                }
            }
            return retVal;
        }

        //Is CRM code is existing in Active
        public static bool IsCRMCodeOrBlackListExistingInActive(string code, long currentID, MySqlConnection con, MySqlTransaction trans)
        {
            bool retVal = false;
            string sqlCommand = "";
            // build SQL statement
            sqlCommand = "Select ID from tbl_CustomerActive " +
                "where ID <> " + currentID.ToString() + " AND Code = '" + DBModule.RefineString(code) + "' AND ARCHIVE=2 AND userid=-1   limit 0,1";
            // run SQL statement
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
            if (ds != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    retVal = true;
                }
            }
            return retVal;
        }
        public static long GetCustIDByNameAndPOLPOD(string uniqueName, string POL, string POD, out double nCont20, out double nCont40, MySqlConnection con, MySqlTransaction trans)
        {
            long retVal = 0;
            double vCont20 = 0;
            double vCont40 = 0;
            string sqlCommand = "";
            // build SQL statement
            sqlCommand = "Select ID,Vols,Report from tbl_CustomerActive " +
                "where  Name = '" + DBModule.RefineString(uniqueName) + "' AND POL = '" + DBModule.RefineString(POL) + "' " +
                "AND Routers = '" + DBModule.RefineString(POD) + "'  AND Archive=2 limit 0,1";
            // run SQL statement
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
            if (ds != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    retVal = long.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                    double.TryParse(ds.Tables[0].Rows[0]["Vols"].ToString(), out vCont20);
                    double.TryParse(ds.Tables[0].Rows[0]["Report"].ToString(), out vCont40);
                }
            }
            nCont20 = vCont20;
            nCont40 = vCont40;
            return retVal;
        }
        public static long GetCustIDByNameAndPOLPODHsCode(string uniqueName, string POL, string POD, string HsNK, string HsXK, out double nCont20, out double nCont40, out int nVolsExt, MySqlConnection con, MySqlTransaction trans)
        {
            long retVal = 0;
            double vCont20 = 0;
            double vCont40 = 0;
            int vVolsExt = 0;
            string sqlCommand = "";
            // build SQL statement
            sqlCommand = "Select ID,Vols,Report,Vols_Ext from tbl_CustomerActive" +
                " WHERE  Name = '" + DBModule.RefineString(uniqueName) + "' AND POL = '" + DBModule.RefineString(POL) + "'" +
                " AND  HsGoods = '" + DBModule.RefineString(HsNK) + "' AND HsGoodsEx = '" + DBModule.RefineString(HsXK) + "'" +
                " AND Routers = '" + DBModule.RefineString(POD) + "'  AND Archive=2 limit 0,1";
            // run SQL statement
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
            if (ds != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    retVal = long.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                    double.TryParse(ds.Tables[0].Rows[0]["Vols"].ToString(), out vCont20);
                    double.TryParse(ds.Tables[0].Rows[0]["Report"].ToString(), out vCont40);// Lay cot Report luu du lieu cua Cont40 trong active cust
                    int.TryParse(ds.Tables[0].Rows[0]["Vols_Ext"].ToString(), out vVolsExt);
                }
            }
            nCont20 = vCont20;
            nCont40 = vCont40;
            nVolsExt = vVolsExt;
            return retVal;
        }
        //  DATE + Mã KH + POL + POD + TYPE + DLVRY + CNEE 
        public static long GetCustIDToImportActiveCust(string Code, string POL, string POD, string HsNK, string HsXK
            , DateTime dateInput, string type, string dlvry, string cnee
            , out double nCont20, out double nCont40, out int nVolsExt, MySqlConnection con, MySqlTransaction trans)
        {
            long retVal = 0;
            double vCont20 = 0;
            double vCont40 = 0;
            int vVolsExt = 0;
            string sqlCommand = "";
            // build SQL statement
            sqlCommand = "Select ID,Vols,Report,Vols_Ext from tbl_CustomerActive" +
                " WHERE  Code = '" + DBModule.RefineString(Code) + "' AND POL = '" + DBModule.RefineString(POL) + "'" +
                " AND  HsGoods = '" + DBModule.RefineString(HsNK) + "' AND HsGoodsEx = '" + DBModule.RefineString(HsXK) + "'" +
                " AND Routers = '" + DBModule.RefineString(POD) + "'" +
                " AND date(LastUpdate) = '" + dateInput.ToString("yyyy-MM-dd") + "'" +
                " AND Active_Type = '" + DBModule.RefineString(type) + "'" +
                " AND Active_Dlvry = '" + DBModule.RefineString(dlvry) + "'" +
                " AND Active_Cnee = '" + DBModule.RefineString(cnee) + "'" +
                " limit 0,1";
            // run SQL statement
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
            if (ds != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    retVal = long.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                    double.TryParse(ds.Tables[0].Rows[0]["Vols"].ToString(), out vCont20);
                    double.TryParse(ds.Tables[0].Rows[0]["Report"].ToString(), out vCont40);// Lay cot Report luu du lieu cua Cont40 trong active cust
                    int.TryParse(ds.Tables[0].Rows[0]["Vols_Ext"].ToString(), out vVolsExt);
                }
            }
            nCont20 = vCont20;
            nCont40 = vCont40;
            nVolsExt = vVolsExt;
            return retVal;
        }
        public static long GetCustomerIDByName(string Name, out long ID, MySqlConnection con, MySqlTransaction trans)
        {
            // build SQL statement
            ID = 0;
            string sqlCommand = "Select ID from tbl_CustomerActive Where Name='" + DBModule.RefineString(Name) + "' limit 0,1";
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
            if (ds != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    ID = long.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
            }
            return ID;
        }

        public static bool HasActionPlan(long custID, MySqlConnection cn, MySqlTransaction trans)
        {
            bool retVal = false;
            string strSQL = "";
            DataSet ds = null;
            strSQL = "SELECT ID,LastUpdate FROM `tbl_CustomerActive` WHERE ID=" + custID + "  LIMIT 0,1 ";
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (DateTime.Parse(ds.Tables[0].Rows[0]["LastUpdate"].ToString()).AddHours(10) >= DateTime.Now)
                {
                    retVal = true;
                }

            }
            return retVal;
        }
        public static void UpdateActiveDuplication(string inputcode, string inputname, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            if (inputcode.Length > 0)
                strSQL += "UPDATE tbl_customeractive set Duplicate=1 WHERE code='" + DBModule.RefineString(inputcode) + "';";
            else if (inputname.Length > 0)
                strSQL += "UPDATE tbl_customeractive set Duplicate=1 WHERE name='" + DBModule.RefineString(inputname) + "';";
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }
    }
}


